package dao.mysql;

import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

//import snaq.db.ConnectionPool;

//import com.mysql.jdbc.Driver;

import dao.ConnPool;
import dao.DAOException;
import dao.EmployeeDAO;
import dto.UserDTO;

//import dao.ConnPool;

public class EmployeeDAOImpl implements EmployeeDAO
{
	// private static final String dbUrl =
	// "jdbc:mysql://localhost:3306/ca_schemata";
	// private static final String dbUserName = "root";
	// private static final String dbPassword = "team1";
	// Connection conn = null;
	// ConnectionPool pool = null;
	// @SuppressWarnings("rawtypes")
	// Class c = null;
	Statement stmt = null;
	ResultSet result = null;

	//
	// public Connection connect() throws DAOException
	// {
	//
	// try
	// {
	// c = Class.forName("com.mysql.jdbc.Driver");
	// Driver driver = (Driver) c.newInstance();
	// DriverManager.registerDriver(driver);
	// pool = new ConnectionPool("local", 1, 5, 20, 180000, dbUrl,
	// dbUserName, dbPassword);
	// }
	// catch (Exception e)
	// {
	// System.out.println("Failed to load JDBC driver.");
	// }
	// try
	// {
	// long timeout = 3000;
	// conn = pool.getConnection(timeout);
	//
	// }
	// catch (Exception e)
	// {
	// System.out.println("Error occured at connect");
	// }
	//
	// if (conn != null)
	// {
	// return conn;
	// }
	// else
	// {
	// throw new DAOException(
	// "Connection Time out, please try again later");
	// }
	//
	// }

	// public void closeconnect()
	// {
	// try
	// {
	//
	// stmt.close();
	// if (result != null)
	// {
	// result.close();
	// }
	// conn.close();
	//
	// }
	// catch (Exception e)
	// {
	// System.out.println("Error occured at closing");
	// System.out.println(e.toString());
	// }
	// }

	public ArrayList<UserDTO> loadUserDTO(ResultSet result)
	{
		ArrayList<UserDTO> userList = new ArrayList<UserDTO>();

		try
		{
			while (result.next())
			{
				UserDTO chosenUser = new UserDTO();
				chosenUser.set_name(result.getString("UserName"));
				chosenUser.set_userID(result.getInt("UserID"));
				chosenUser.set_password(result.getString("Password"));
				chosenUser.set_age(result.getInt("Age"));
				chosenUser.set_annualLeaveEntitlement(result
						.getDouble("AnnualLeaveEntitlement"));
				chosenUser.set_annualLeave(result.getDouble("AnnualLeaveLeft"));
				chosenUser.set_compensationLeave(result
						.getDouble("CompensationLeaveLeft"));
				chosenUser.set_domain(result.getString("Domain"));
				chosenUser.set_gender(result.getString("Gender"));
				chosenUser.set_medicalLeaveEntitlement(result
						.getDouble("MedicalLeaveEntitlement"));
				chosenUser.set_medicalLeave(result
						.getDouble("MedicalLeaveTaken"));
				chosenUser.set_overTimeDays(result.getDouble("OverTimeDays"));
				chosenUser.set_staffType(result.getString("StaffType"));
				chosenUser.set_supID(result.getInt("SupervisorID"));

				userList.add(chosenUser);
			}
		}
		catch (Exception e)
		{
			System.out.println("Error Occurred at load data");
			System.out.println(e.toString());
		}

		return userList;
	}

	public UserDTO findUser(int userID) throws DAOException
	{
		UserDTO temp = null;

		try
		{
			Connection conn = ConnPool.GetConnection();
			stmt = conn.createStatement();

			String sql = String.format(
					"Select * from ca_schemata.accountinfo where UserID=%d",
					userID);

			result = stmt.executeQuery(sql);

			temp = loadUserDTO(result).get(0);
		}
		catch (Exception e)
		{
			UserDTO chosenUser = new UserDTO();
			System.out.println("Error occured at finding user");
			System.out.println(e.toString());
			chosenUser = null;
			temp = chosenUser;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
		return temp;
	}

	public boolean login(int userID, String password, String role)
			throws DAOException
	{
		// connect();
		boolean success = false;
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String.format(
					"Select * from ca_schemata.accountinfo where UserID=%d",
					userID);

			result = stmt.executeQuery(sql);
			// System.out.println(result.next());

			if (result.first())
			{
				String userpw = result.getString("Password");

				if (userpw.equals(password))
				{
					if (role.equals(result.getString("Domain")))
					{
						success = true;
					}
					else
						success = false;
				}

				else
				{
					success = false;
				}
			}
			else
			{
				success = false;
			}

		}
		catch (Exception e)
		{
			System.out.println("Login error");
			System.out.println(e.toString());
			success = false;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
		return success;
	}

	public ArrayList<UserDTO> findAllUser() throws DAOException
	{
		ArrayList<UserDTO> userList = new ArrayList<UserDTO>();

		// connect();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String.format("Select * from ca_schemata.accountinfo");

			result = stmt.executeQuery(sql);

			userList = loadUserDTO(result);
		}
		catch (Exception e)
		{
			System.out.println("find all error");
			System.out.println(e.toString());
			userList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return userList;
	}

	public void insertUser(UserDTO user) throws DAOException
	{
		// connect();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("INSERT INTO ca_schemata.accountinfo (UserName,Password,Domain,SupervisorID,Gender,Age,StaffType,AnnualLeaveEntitlement, AnnualLeaveLeft,MedicalLeaveEntitlement, MEdicalLeaveTaken,CompensationLeaveLeft,OverTimeDays) VALUES ('%s','%s','%s',%d,'%s',%d,'%s',%f,%f,%f,%f,%f,%f)",
							user.get_name(), user.get_password(),
							user.get_domain(), user.get_supID(),
							user.get_gender(), user.get_age(),
							user.get_staffType(),
							user.get_annualLeaveEntitlement(),
							user.get_annualLeave(),
							user.get_medicalLeaveEntitlement(),
							user.get_medicalLeave(),
							user.get_compensationLeave(),
							user.get_overTimeDays());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("insert user error");
			System.out.println(e.toString());
		}
		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

	}

	public void updateUser(UserDTO user) throws DAOException
	{
		// connect();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Update ca_schemata.accountinfo SET UserName = '%s', Password = '%s', Domain = '%s', SupervisorID = %d, Gender = '%s', Age = %d, StaffType = '%s',AnnualLeaveEntitlement = %f, AnnualLeaveLeft = %f,MedicalLeaveEntitlement = %f, MedicalLeaveTaken = %f,CompensationLeaveLeft = %f, OverTimeDays = %f WHERE UserID=%d",
							user.get_name(), user.get_password(),
							user.get_domain(), user.get_supID(),
							user.get_gender(), user.get_age(),
							user.get_staffType(),
							user.get_annualLeaveEntitlement(),
							user.get_annualLeave(),
							user.get_medicalLeaveEntitlement(),
							user.get_medicalLeave(),
							user.get_compensationLeave(),
							user.get_overTimeDays(), user.get_userID());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at update");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	public void updateUserLeaveDays(int userID, double Adays, double Mdays,double Cdays)
			throws DAOException
	{
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Update ca_schemata.accountinfo SET  AnnualLeaveLeft = %f,  MedicalLeaveTaken = %f , CompensationLeaveLeft = %f WHERE UserID=%d",
							Adays, Mdays,Cdays, userID);

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at update");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	public void deleteUser(UserDTO user) throws DAOException
	{
		// connect();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String.format(
					"Delete From ca_schemata.accountinfo WHERE UserID=%d",
					user.get_userID());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at delete");
			System.out.println(e.toString());
		}
		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	@Override
	public void updateUserByAdmin(UserDTO user) throws DAOException
	{
		// TODO Auto-generated method stub
		String sql = null;
		try
		{
			stmt = ConnPool.GetConnection().createStatement();
			if (!user.get_password().equals(""))
			{
				sql = String
						.format("Update ca_schemata.accountinfo SET UserName = '%s', Password = '%s', Domain = '%s', SupervisorID = %d, Gender = '%s', Age = %d, StaffType = '%s',AnnualLeaveEntitlement = %f WHERE UserID=%d",
								user.get_name(), user.get_password(),
								user.get_domain(), user.get_supID(),
								user.get_gender(), user.get_age(),
								user.get_staffType(),
								user.get_annualLeaveEntitlement(),
								user.get_userID());
			}

			else if (user.get_password().equals(""))
			{
				sql = String
						.format("Update ca_schemata.accountinfo SET UserName = '%s', Domain = '%s', SupervisorID = %d, Gender = '%s', Age = %d, StaffType = '%s',AnnualLeaveEntitlement = %f WHERE UserID=%d",
								user.get_name(), user.get_domain(),
								user.get_supID(), user.get_gender(),
								user.get_age(), user.get_staffType(),
								user.get_annualLeaveEntitlement(),
								user.get_userID());
			}
			
			
			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at update");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

	}

	// @Override
	// public void updateUserCompensationLeave(int userID, double compDays)
	// throws DAOException
	// {
	// // TODO Auto-generated method stub
	// try
	// {
	// stmt = ConnPool.GetConnection().createStatement();
	//
	// String sql = String
	// .format("Update ca_schemata.accountinfo SET  CompensationLeaveLeft = %f WHERE UserID=%d",
	// compDays, userID);
	//
	// stmt.execute(sql);
	// }
	// catch (Exception e)
	// {
	// System.out.println("Error occured at update compensation leave");
	// System.out.println(e.toString());
	// }
	//
	// finally
	// {
	// ConnPool.closeconnect(stmt, result);
	// }
	// }

}
